﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading;
using System.Threading.Tasks;
using System.Windows.Forms;
using Excel = Microsoft.Office.Interop.Excel;
using Ookii.Dialogs.Wpf;

namespace Excel合并
{
    public partial class Form1 : Form
    {
        // 文件计数
        private int file = 0;
        // 公共线程
        public Thread starts;
        // 创建一个集合，这个集合主要存放多个文件的数据
        private List<string> listexcel = new List<string>();
        public Form1()
        {
            InitializeComponent();
            // 窗体居中
            StartPosition = FormStartPosition.CenterScreen;
            // 禁用控件安全的检查
            Control.CheckForIllegalCrossThreadCalls = false;
        }

        private void groupBox1_Enter(object sender, EventArgs e)
        {

        }

        private void checkBox5_CheckedChanged(object sender, EventArgs e)
        {
            if (checkBox5.Checked == true)
            {
                checkBox4.Checked = false;
                checkBox3.Checked = false;
                checkBox6.Checked = false;
            }
        }

        private void button1_Click(object sender, EventArgs e)
        {
            // 先清除内容
            textBox1.Clear();
            textBox2.Clear();
            if(checkBox1.Checked == true)
            {
                // 单文件模式，打开文件
                OpenFileDialog openFileDialog = new OpenFileDialog();
                openFileDialog.Filter = "Excel文件|*.xlsx;*.xls|CSV文件|*.csv";
                openFileDialog.Title = "选择要打开的文件";

                if (openFileDialog.ShowDialog() == DialogResult.OK)
                {
                    string filePath = openFileDialog.FileName;
                    textBox1.Text = filePath;
                    richTextBox1.Text += DateTime.Now.ToString() + "-----" + "选择文件：" + filePath + "\r\n";
                    // 默认显示最后一行
                    richTextBox1.SelectionStart = richTextBox1.Text.Length;
                    richTextBox1.ScrollToCaret();
                }
            }
            else if(checkBox2.Checked == true)
            {
                // 多文件模式，打开文件夹，遍历所有文件
                var folderDialog = new VistaFolderBrowserDialog();
                if (folderDialog.ShowDialog() == true)
                {
                    string folderPath = folderDialog.SelectedPath;
                    ExploreFiles(folderPath);
                }
            }
        }
        private void ExploreFiles(string folderPath)
        {
            if (Directory.Exists(folderPath))
            {
                string[] files = Directory.GetFiles(folderPath, "*", SearchOption.AllDirectories);

                foreach (string filePath in files)
                {
                    file++;
                    textBox1.Text = Convert.ToString(file) + "个文件";
                    richTextBox1.Text += DateTime.Now.ToString() + "-----" + "选择文件：" + filePath + "\r\n";
                    // 默认显示最后一行
                    richTextBox1.SelectionStart = richTextBox1.Text.Length;
                    richTextBox1.ScrollToCaret();
                    Console.WriteLine(filePath);
                    // 将结果写入到集合中
                    listexcel.Add(filePath);
                }
            }
            else
            {
                MessageBox.Show($"所选文件夹 {folderPath} 不存在！");
            }
        }
        private void checkBox1_CheckedChanged(object sender, EventArgs e)
        {
            if(checkBox1.Checked == true)
            {
                checkBox2.Checked = false;
            }
        }

        private void checkBox2_CheckedChanged(object sender, EventArgs e)
        {
            if(checkBox2.Checked == true)
            {
                checkBox1.Checked = false;
            }
        }

        private void button2_Click(object sender, EventArgs e)
        {
            //using (var folderDialog = new FolderInputDialog())
            //{
            //    if (folderDialog.ShowDialog() == DialogResult.OK)
            //    {
            //        string folderPath = folderDialog.FolderPath;
            //        textBox2.Text = folderPath;
            //    }
            //}

            using (FolderBrowserDialog folderBrowserDialog = new FolderBrowserDialog())
            {
                folderBrowserDialog.Description = "请选择需要保存的文件夹";
                folderBrowserDialog.RootFolder = Environment.SpecialFolder.MyComputer;

                if (folderBrowserDialog.ShowDialog() == DialogResult.OK)
                {
                    // 选择保存的文件夹
                    string filpath = folderBrowserDialog.SelectedPath;
                    textBox2.Text = filpath;
                }
            }
        }

        private void Form1_Load(object sender, EventArgs e)
        {
            // 按钮禁用
            button4.Enabled = false;
            button3.Enabled = true;
        }

        private void button3_Click(object sender, EventArgs e)
        {
            if(textBox1.Text.Length < 1)
            {
                MessageBox.Show("注意！你没有选择需要合并的文件夹或文件！");
                return;
            }
            else if(textBox2.Text.Length < 1)
            {
                MessageBox.Show("注意！你没有选择保存合并后的文件夹！");
                return;
            }
            else
            {
                if (checkBox1.Checked == true)
                {
                    // 创建单线程来执行任务
                    starts = new Thread(StartExcel);
                    starts.Start();
                    starts.IsBackground = false;
                }
                else if (checkBox2.Checked == true)
                {
                    // 创建单线程来执行任务
                    starts = new Thread(StartExcelS);
                    starts.Start();
                    starts.IsBackground = false;
                }
            }
        }
        /// <summary>
        /// 单线程执行单挑数据合并
        /// </summary>
        private void StartExcel()
        {
            // 创建一个新的 Excel 应用程序实例
            Excel.Application excelApp = new Excel.Application();

            // 设置为不显示 Excel 程序窗口
            excelApp.Visible = false;

            // 需要合并的工作簿文件路径
            string workbookPath = textBox1.Text;

            // 打开工作簿文件
            Excel.Workbook workbook = excelApp.Workbooks.Open(workbookPath);

            // 获取第一个工作表
            Excel.Worksheet worksheet = workbook.Sheets[1];

            // 创建一个新的工作簿用于合并后的结果
            Excel.Workbook mergedWorkbook = excelApp.Workbooks.Add();
            Excel.Worksheet mergedWorksheet = mergedWorkbook.Sheets[1];

            // 获取第一个工作表的数据范围
            Excel.Range sourceRange = worksheet.UsedRange;

            // 将数据复制到合并后的工作表中
            Excel.Range destinationRange = mergedWorksheet.Cells[1, 1];
            sourceRange.Copy(destinationRange);

            // 保存合并后的工作簿
            string outputPath = textBox2.Text + @"news.xlsx";
            mergedWorkbook.SaveAs(outputPath);

            // 关闭打开的工作簿文件
            workbook.Close();

            // 关闭合并后的工作簿
            mergedWorkbook.Close();

            // 关闭 Excel 应用程序
            excelApp.Quit();

            MessageBox.Show("合并完成！" + "\r\n" + "新文件路径是：" + outputPath);
        }
        /// <summary>
        /// 多线程来执行多条数据合并
        /// </summary>
        private void StartExcelS()
        {
            // 使用多线程的方式来执行
            try
            {
                if(checkBox3.Checked == true)
                {
                    // 工作表合并
                    Excelhbone();
                }
                else if (checkBox4.Checked == true)
                {
                    // 工作簿合并
                    Excelhbtow();
                }
                else if (checkBox5.Checked == true)
                {
                    // 工作表拆分
                    ExcelCs();
                }
                else if(checkBox6.Checked == true)
                {
                    // 工作表递增合并
                    ExcelDz();
                }
                else if(checkBox7.Checked == true)
                {
                    starts = new Thread(SheetCode);
                    starts.Start();
                    starts.IsBackground = false;
                }
                foreach (var code in listexcel)
                {
                    // 循环读取集合中的数据
                    Console.WriteLine("集合中的数据为：" + code);
                    richTextBox1.Text += DateTime.Now.ToString() + "获取多数据任务数据：" + "-----" + code + "\r\n";
                    // 默认显示最后一行
                    richTextBox1.SelectionStart = richTextBox1.Text.Length;
                    richTextBox1.ScrollToCaret();
                }
            }
            catch(Exception ex)
            {
                // 获取集合异常
                MessageBox.Show(ex.ToString());
            }
        }
        private void checkBox3_CheckedChanged(object sender, EventArgs e)
        {
            if(checkBox3.Checked == true)
            {
                checkBox4.Checked = false;
                checkBox5.Checked = false;
                checkBox6.Checked = false;
            }
        }

        private void checkBox4_CheckedChanged(object sender, EventArgs e)
        {
            if (checkBox4.Checked == true)
            {
                checkBox3.Checked = false;
                checkBox5.Checked = false;
                checkBox6.Checked = false;
            }
        }

        private void checkBox6_CheckedChanged(object sender, EventArgs e)
        {
            if (checkBox6.Checked == true)
            {
                checkBox4.Checked = false;
                checkBox5.Checked = false;
                checkBox3.Checked = false;
            }
        }

        private void button4_Click(object sender, EventArgs e)
        {
            try
            {
                // 停止线程
                if (starts.ThreadState != ThreadState.Running)
                {
                    // 线程终止方式一
                    starts.Abort();
                    // 线程终止方式二
                }
            }
            catch(Exception ex)
            {
                Console.WriteLine(ex.ToString());
            }
        }
        /// <summary>
        /// 工作表合并
        /// </summary>
        public void Excelhbone()
        {
            // 创建一个新的 Excel 应用程序实例
            Excel.Application excelApp = new Excel.Application();

            // 设置为不显示 Excel 程序窗口
            excelApp.Visible = false;

            // 创建一个新的工作簿用于合并后的结果
            Excel.Workbook mergedWorkbook = excelApp.Workbooks.Add();
            Excel.Worksheet mergedWorksheet = mergedWorkbook.Sheets[1];

            int mergedRow = 1; // 合并后的行索引

            foreach (string workbookPath in listexcel)
            {
                // 打开工作簿文件
                Excel.Workbook workbook = excelApp.Workbooks.Open(workbookPath);

                // 获取第一个工作表
                Excel.Worksheet worksheet = workbook.Sheets[1];

                // 获取第一个工作表的数据范围
                Excel.Range sourceRange = worksheet.UsedRange;

                // 将数据复制到合并后的工作表中
                Excel.Range destinationRange = mergedWorksheet.Cells[mergedRow, 1];
                sourceRange.Copy(destinationRange);

                // 更新合并后的行索引
                mergedRow += sourceRange.Rows.Count;

                // 关闭打开的工作簿文件
                workbook.Close();
            }

            // 保存合并后的工作簿
            string outputPath = textBox2.Text + @"\Merged.xlsx";
            mergedWorkbook.SaveAs(outputPath);

            // 关闭合并后的工作簿
            mergedWorkbook.Close();

            // 关闭 Excel 应用程序
            excelApp.Quit();

            MessageBox.Show("合并完成！" + "\r\n" + "新文件路径是：" + outputPath);
        }
        /// <summary>
        /// 工作簿合并
        /// </summary>
        public void Excelhbtow()
        {

        }
        /// <summary>
        /// 工作表拆分
        /// </summary>
        public void ExcelCs()
        {

        }
        /// <summary>
        /// 工作表递增合并
        /// </summary>
        public void ExcelDz()
        {

        }

        private void checkBox7_CheckedChanged(object sender, EventArgs e)
        {
            if(checkBox7.Checked == false)
            {
                checkBox3.Checked = true;
                checkBox8.Checked = true;
                checkBox3.Enabled = true;
                checkBox8.Enabled = true;
                textBox3.Enabled = false;
                textBox4.Enabled = false;
            }
            else if(checkBox7.Checked == true)
            {
                checkBox3.Checked = false;
                checkBox8.Checked = false;
                checkBox3.Enabled = false;
                checkBox8.Enabled = false;
                textBox3.Enabled = true;
                textBox4.Enabled = true;
            }
        }
        private void SheetCode()
        {
            if (!int.TryParse(textBox3.Text, out int fromSheet) || !int.TryParse(textBox4.Text, out int toSheet))
            {
                MessageBox.Show("请输入sheet值！");
                return;
            }
            // 保存的文件夹路径
            string outputFilePath = textBox2.Text + @"\new.xlsx";

            Excel.Application excelApp = new Excel.Application();
            Excel.Workbook outputWorkbook = excelApp.Workbooks.Add();

            int sheetIndex = 0;

            for (int i = fromSheet; i <= toSheet; i++)
            {
                sheetIndex++;
                Excel.Worksheet mergedSheet = outputWorkbook.Sheets.Add(After: outputWorkbook.Sheets[outputWorkbook.Sheets.Count]);
                mergedSheet.Name = $"合并{i}";

                int rowOffset = 1;

                foreach (var filePath in listexcel)
                {
                    Excel.Workbook workbook = excelApp.Workbooks.Open(filePath);
                    Excel.Worksheet sourceSheet = workbook.Sheets[i];

                    CopySheet(sourceSheet, mergedSheet, ref rowOffset);

                    workbook.Close(false);
                }
            }

            // 删除默认创建的空白工作表
            if (outputWorkbook.Sheets.Count > 1)
            {
                Excel.Worksheet tempSheet = outputWorkbook.Sheets[1];
                tempSheet.Delete();
            }

            outputWorkbook.SaveAs(outputFilePath);
            outputWorkbook.Close(false);
            excelApp.Quit();

            MessageBox.Show("多文件合并完成！" + "\r\n" + "文件保存路径为：" + outputFilePath);
        }
        static void CopySheet(Excel.Worksheet sourceSheet, Excel.Worksheet targetSheet, ref int rowOffset)
        {
            Excel.Range sourceRange = sourceSheet.UsedRange;
            Excel.Range targetRange = targetSheet.Cells[rowOffset, 1];
            sourceRange.Copy(targetRange);

            rowOffset += sourceRange.Rows.Count;
        }

    }
}
